﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;



namespace DAL.BusinessDeal
{
    public class StSpProcessCargoAmountDAL
    {
        private const string selectProcessVoytageDAL = null;

        private const string selectStr = @"select vca.*,src.rv_meaning 
from st_voyage_cargo_amounts vca left join st_ref_codes src on vca.vag_gtypecd= src.rv_low_value {0}";

        private const string deleteStr = @"DELETE FROM st_voyage_cargo_amounts WHERE VAG_ID ={0} ";

        /// <summary>
        /// Get DataTable
        /// </summary>
        /// <param name="textWhere">conditon</param>
        /// <param name="orderText">sort</param>
        /// <returns></returns>
        public DataTable GetTable()
        {
            return GetDataTable(selectProcessVoytageDAL);
        }

        /// <summary>
        /// 得到散货箱量
        /// </summary>
        /// <param name="textWhere"></param>
        /// <param name="orderText"></param>
        /// <returns></returns>
        public DataTable GetTable(string textWhere, string orderText)
        {
            string query = string.Empty;
            if (!string.IsNullOrEmpty(textWhere))
                query += " where " + textWhere;
            if (!string.IsNullOrEmpty(orderText))
                query += " order by " + orderText;

            return GetDataTable(string.Format(selectStr, query));
        }

        /// <summary>
        /// Get DataTable
        /// </summary>
        /// <param name="sql">SQL statement</param>
        /// <returns></returns>
        public DataTable GetDataTable(string sql)
        {
            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetSqlStringCommand(sql);
            DataTable dt = db.ExecuteDataTable(dbCommand);
            if (dt != null)
            {
                dt.TableName = "SP_Process_Cargo_Amount";
            }
            return dt;
        }

        public string InsertUpdateProcessCargomount(int P_Operation_Type, string P_VOY_ID,string P_VAG_ID, string P_VAG_GTYPECD,
           string P_VAG_PKG, string P_VAG_TON, string P_VAG_VOL, string P_VAG_REMARK, string P_USER_ID,string P_SCD_ID)
        {

            Database db = DatabaseFactory.CreateDatabase();
            DbCommand dbCommand = db.GetStoredProcCommand("web_sp_process_cargo_amount");
            db.AddInParameter(dbCommand, "P_Operation_Type", DbType.Int32, P_Operation_Type);
            db.AddInParameter(dbCommand, "P_VOY_ID", DbType.Int32, P_VOY_ID);//P_VAG_ID
            db.AddInParameter(dbCommand, "P_VAG_ID", DbType.Int32, P_VAG_ID);//P_VAG_ID
            db.AddInParameter(dbCommand, "P_VAG_GTYPECD", DbType.String, P_VAG_GTYPECD);
            db.AddInParameter(dbCommand, "P_VAG_PKG", DbType.Int32, P_VAG_PKG);
            db.AddInParameter(dbCommand, "P_VAG_TON", DbType.Int32, P_VAG_TON);
            db.AddInParameter(dbCommand, "P_VAG_VOL", DbType.Int32, P_VAG_VOL);
            db.AddInParameter(dbCommand, "P_VAG_REMARK", DbType.String, P_VAG_REMARK);
            db.AddInParameter(dbCommand, "P_USER_ID", DbType.Int32, P_USER_ID);
            db.AddInParameter(dbCommand, "P_SCD_ID", DbType.Int32, P_SCD_ID);

            db.AddOutParameter(dbCommand, "ReturnValue", DbType.String, 1000);
            dbCommand.Parameters["ReturnValue"].Direction = ParameterDirection.Output;
            db.ExecuteNonQuery(dbCommand);
            return dbCommand.Parameters["ReturnValue"].Value.ToString();

        }


        /// <summary>
        /// 批量删除表
        /// </summary>
        /// <param name="mList"></param>
        /// <returns></returns>
        public bool DeleteProcessCargomount(List<string> mList)
        {
            string sql = string.Empty;
            if (mList.Count <= 0)
                return false;

            Database db = DatabaseFactory.CreateDatabase();
            bool isOK = false;
            using (DbConnection conn = db.CreateConnection())
            {
                conn.Open();
                using (DbTransaction tran = conn.BeginTransaction())
                {
                    try
                    {
                        foreach (string VATID in mList)
                        {
                            sql = string.Empty;
                            sql = string.Format(deleteStr, int.Parse(VATID));
                            db.ExecuteNonQuery(tran, CommandType.Text, sql);
                        }

                        tran.Commit();
                        isOK = true;
                    }
                    catch (Exception ex)
                    {
                        tran.Rollback();
                        isOK = false;
                    }
                }
                conn.Close();
            }
            return isOK;
        }
    }//end class
}
